import pymysql

conn = pymysql.connect(
    host="localhost",
    port=3306,
    database="db1",
    user="root",
    password="123456",
    charset="utf8"
)
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)  # 括号里面是光标返回数据类型，可不写，默认元组

# 增
# sql = "insert into students(id,user,age,sex,password) values(3,'alex',18,'女','1233')"
# 删
# sql = "delect from students where id=%s"
# 改
# sql = "update students set age=%s,sex=%s where id=3;"
# 查    不需要提交
sql = "select * from students"

try:
    # cursor.execute(sql,[18,'女'])
    # 提交
    # conn.commit()

    num = cursor.execute(sql)
    # ret = cursor.fetchall()     # 返回所有数据
    # print(ret)
    for i in range(0,num):
        ret = cursor.fetchone()     # 返回数据的一行
        print(ret)
        print(ret['user'],":",ret['password'])

    # 光标按绝对位置移动
    # cursor.scroll(1,mode="absolute")
    # 光标按相对位置(当前位置)移动1
    # cursor.scroll(1,mode="relative")

    # 获取刚刚插入的数据的id，可做后续操作
    # last_id = cursor.lastrowid
    
    # 批量插入数据 1. 2.
    # data = [(11,"男"),(12,"女"),(18,"女")]
    # for i in data:                # 1.
    #     cursor.execute(sql,i)

    # cursor.executemany(sql,data)  # 2.

except Exception as e:
    print("ERROR:",str(e))
    conn.rollback()     # 事务回滚

# 关闭资源
cursor.close()
conn.close()


